package com.roc.web.action.agent;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;

public class MerinfoStatisticsSearchCondition extends BaseSearchCondition {

	
	private String 	minDate; // 交易开始时间(年月日)
	private String 	maxDate; // 交易结束时间(年月日)
	private String 	agentId; // 代理商id

	private String orgCode;// 机构号
	
	private String obj;//代理商id

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getOrderPartOfHQL()
	 */
	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		
		return " ORDER BY A.MCHT_CD  ";
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getRowCountHQL()
	 */
	@Override
	protected String getRowCountHQL() {// 48502000   85278888
		StringBuffer sb=new StringBuffer();

		sb.append( " SELECT count(*) " );
		sb.append( "  FROM TBL_TERM_INF B, " );
		sb.append( "       (SELECT M.*,I.INS_NAME, TT.RNAME " );
		sb.append( "          FROM TBL_MCHT_INF M " );
		sb.append( "          LEFT JOIN TBL_INS_INF I " );
		sb.append( "            ON M.BANK_BELONG_CD = I.INS_ID_CD " );

		if(obj!=null&&!obj.equals("")){
			sb.append( "         , (SELECT TO_CHAR(T.ID) TID, T.REAL_NAME RNAME " );
			sb.append( "                  FROM T_OPERATORS T " );
			sb.append( "                 START WITH T.ID = '" + obj + "' " );
			sb.append( "                        AND T.ROLE_TYPE = '03' " );
			sb.append( "                CONNECT BY PRIOR T.ID = T.SUPRI_ID " );
			sb.append( "                 ORDER BY T.ID DESC) TT " );
		}
		sb.append( "         WHERE 1 = 1 " );

		if(obj!=null&&!obj.equals("")){ 
			sb.append(" and  M.EXT1 = TT.TID " );
		}

		if (Constance.isNotEmpty(minDate) && Constance.isNotEmpty(maxDate)) {
			String startDate = minDate.replace("-", "");
			String endDate = maxDate.replace("-", "");
			
			sb.append( "           AND TO_CHAR(M.REC_CRT_TS, 'yyyyMMdd') >= '" + startDate + "' " );
			sb.append( "           AND TO_CHAR(M.REC_CRT_TS, 'yyyyMMdd') <= '" + endDate + "' " );
		}
		sb.append( " ) A " );
		
		sb.append( "  LEFT JOIN T_OPERATORS O " );
		sb.append( "    ON O.ID = A.ORI_CHNL_DESC " );
		sb.append( " WHERE B.MCHT_CD = A.MCHT_CD " );
		return sb.toString();
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getSearchHQL()
	 */
	@Override
	protected String getSearchHQL() {
		StringBuffer sb=new StringBuffer();

		sb.append( " SELECT A.APPL_DATE, " );
		sb.append( "       A.MCHT_CD, " );
		sb.append( "       B.TERM_ID, " );
		sb.append( "       A.NAME_BUSI, " );
		sb.append( "       A.NAME, " );
		sb.append( "       A.BUSI_LICE_NO, " );
		sb.append( "       A.INS_NAME, " );
		sb.append( "       A.RNAME, " );
		sb.append( "       O.REAL_NAME, " );
		sb.append( "       A.STATUS, " );
		sb.append( "       A.CONTACT_NAME, " );
		sb.append( "       A.CONTACT_PHONENO, " );
		sb.append( "       A.CERTIF, " );
		sb.append( "       A.CERTIF_NO, " );
		sb.append( "       A.UP_MCC_CD, " );
		sb.append( "       A.REG_ADDR, " );
		sb.append( "       B.TERM_TP, " );
		sb.append( "       B.INSTALLLOCATION " );
		sb.append( "  FROM TBL_TERM_INF B, " );
		sb.append( "       (SELECT M.*,I.INS_NAME, TT.RNAME " );
		sb.append( "          FROM TBL_MCHT_INF M " );
		sb.append( "          LEFT JOIN TBL_INS_INF I " );
		sb.append( "            ON M.BANK_BELONG_CD = I.INS_ID_CD " );

		if(obj!=null&&!obj.equals("")){
			sb.append( "         , (SELECT TO_CHAR(T.ID) TID, T.REAL_NAME RNAME " );
			sb.append( "                  FROM T_OPERATORS T " );
			sb.append( "                 START WITH T.ID = '" + obj + "' " );
			sb.append( "                        AND T.ROLE_TYPE = '03' " );
			sb.append( "                CONNECT BY PRIOR T.ID = T.SUPRI_ID " );
			sb.append( "                 ORDER BY T.ID DESC) TT " );
		}
		sb.append( "         WHERE 1 = 1 " );

		if(obj!=null&&!obj.equals("")){ 
			sb.append(" and  M.EXT1 = TT.TID " );
		}

		if (Constance.isNotEmpty(minDate) && Constance.isNotEmpty(maxDate)) {
			String startDate = minDate.replace("-", "");
			String endDate = maxDate.replace("-", "");
			
			sb.append( "           AND TO_CHAR(M.REC_CRT_TS, 'yyyyMMdd') >= '" + startDate + "' " );
			sb.append( "           AND TO_CHAR(M.REC_CRT_TS, 'yyyyMMdd') <= '" + endDate + "' " );
		}
		sb.append( " ) A " );
		
		sb.append( "  LEFT JOIN T_OPERATORS O " );
		sb.append( "    ON O.ID = A.ORI_CHNL_DESC " );
		sb.append( " WHERE B.MCHT_CD = A.MCHT_CD " );
				
		return sb.toString();
		/*return hql;*/
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getWhereHQLFieldBeanList()
	 */
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
	/*	
		if (Constance.isNotEmpty(minDate) && Constance.isNotEmpty(maxDate)) {
			//Date startDate = Constance.formateDate(minDate);
			//Date endDate = Constance.formateDate(maxDate);
			String startDate = minDate.replace("-", "");
			String endDate = maxDate.replace("-", "");
			filedBeanList.add(new WhereHQLFieldBean("to_char(s.DT_CREATE,'yyyyMMdd')", startDate,
					">=", false));
			filedBeanList.add(new WhereHQLFieldBean("to_char(s.DT_CREATE,'yyyyMMdd')", endDate,
					"<=", false));
		}*/
	//	if(obj != null){
	//		filedBeanList.add(new WhereHQLFieldBean("o.id", obj, "IN", false));
	//	}
		/*if(agentIdList!= null && agentIdList.length > 0){//下级代理商
        	filedBeanList.add(new WhereHQLFieldBean("s.id IN("+Constance.getArrayContent(agentIdList)+")"));
        }*/
		return filedBeanList;
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}

	public String getAgentId() {
		return agentId;
	}

	public void setAgentId(String agentId) {
		this.agentId = agentId;
	}

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	public String getObj() {
		return obj;
	}

	public void setObj(String obj) {
		this.obj = obj;
	}

}
